Uploading Profile Data to RDS and S3 using Node.js

In modern web applications, user profiles often include textual data (such as name and email) and a profile picture. In this blog, we'll walk through how to:

- Insert user profile data into Amazon RDS.
- Upload a profile picture to Amazon S3.
- Store the image URL in the RDS database.

We'll use

**Node.js**,

**Express.js**,

**AWS SDK**,

**Multer**, and

**MySQL**.

Prerequisites

Before we begin, ensure you have:

- An AWS account with **S3** and **RDS** set up.
- Node.js and npm installed.
- MySQL database instance in Amazon RDS.

Step 1: Setting Up Dependencies

Install the required packages:

```sh
npm init -y
npm install express mysql2 aws-sdk multer multer-s3 dotenv
```

- `express`: Web framework.
- `mysql2`: Connect to MySQL RDS.
- `aws-sdk`: Interact with AWS S3.
- `multer` & `multer-s3`: Handle file uploads.
- `dotenv`: Manage environment variables.

Step 2: Configure AWS and MySQL Connection

Create a `.env` file to store credentials:

```
AWS_ACCESS_KEY_ID=your_access_key
AWS_SECRET_ACCESS_KEY=your_secret_key
AWS_REGION=your_region
S3_BUCKET_NAME=your_bucket_name

DB_HOST=your_rds_host
DB_USER=your_rds_user
DB_PASSWORD=your_rds_password
DB_DATABASE=your_rds_database
```

Now, configure AWS SDK and MySQL in `db.js`:

```js
require("dotenv").config();
const mysql = require("mysql2");
const AWS = require("aws-sdk");
const s3 = new AWS.S3({
  accessKeyId: process.env.AWS_ACCESS_KEY_ID,
  secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY,
  region: process.env.AWS_REGION
});

const db = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE
});

db.connect(err => {
  if (err) {
    console.error("Database connection error: ", err);
  } else {
    console.log("Connected to RDS MySQL!");
  }
});

module.exports = { db, s3 };
```

Step 3: Set Up File Upload with Multer-S3

In `upload.js`, configure Multer to upload files to S3:

```js
const multer = require("multer");
const multerS3 = require("multer-s3");
const { s3 } = require("./db");

const upload = multer({
  storage: multerS3({
    s3: s3,
    bucket: process.env.S3_BUCKET_NAME,
    acl: "public-read",
    metadata: (req, file, cb) => {
      cb(null, { fieldName: file.fieldname });
    },
    key: (req, file, cb) => {
      cb(null, `profiles/${Date.now()}_${file.originalname}`);
    }
  })
});

module.exports = upload;
```

Step 4: API Route to Upload Profile and Save Data

Create an `index.js` file and add the following API endpoint:

```js
const express = require("express");
const { db } = require("./db");
const upload = require("./upload");

const app = express();
app.use(express.json());

app.post("/upload-profile", upload.single("profilePic"), (req, res) => {
  const { name, email } = req.body;
  const profileImageUrl = req.file.location;

  const sql = "INSERT INTO users (name, email, profile_pic) VALUES (?, ?, ?)";
  db.query(sql, [name, email, profileImageUrl], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json({ message: "Profile uploaded successfully!", userId: result.insertId });
  });
});

app.listen(3000, () => console.log("Server running on port 3000"));
```

Step 5: Creating the MySQL Table

Before running the API, create the `users` table in RDS:

```sql
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  profile_pic VARCHAR(255)
);
```

Step 6: Running and Testing the API

Start the server:

```
node index.js
```

Test the API using Postman